/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.RoleDTO;
import dto.RoomDTO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import util.DBConfig;

/**
 *
 * @author aptech
 */
public class RoomDAO {

    DBConfig con;
    Connection connect;
    ArrayList<RoomDTO> list;

    public RoomDAO() {
        con = new DBConfig();
        list = new ArrayList<RoomDTO>();
    }
    String SQL_CREATE = "insert into Rooms values(?,?,?,?)";
    String SQL_READALL = "Select * from Rooms";
    String SQL_READ_BYID = "select * from Rooms where RoomID = ?";
    String SQL_READ_BY_TYPEID = "select * from Rooms where typeID = ?";
    String SQL_UPDATE = "update Rooms set RoomNo=?,  RoomFloor=?, Bed=?, typeID=? where RoomID=?";
    String SQL_DELETE = "delete Rooms where RoomID=?";

    public int create(RoomDTO dto) {
        try {
            ResultSet generatedKeys = null;
            connect = con.OpenConnect();

            PreparedStatement pst = connect.prepareStatement(SQL_CREATE, PreparedStatement.RETURN_GENERATED_KEYS);
            pst.setString(1, dto.getRoomno());
            pst.setInt(2, dto.getRoomfloor());
            pst.setInt(3, dto.getBeds());
            pst.setInt(4, dto.getTypeid());
            int s = pst.executeUpdate();
            generatedKeys = pst.getGeneratedKeys();
            if (generatedKeys.next()) {
                long id = generatedKeys.getLong(1);
                return Integer.parseInt(String.valueOf(id));
            }
            return 0;
        } catch (SQLException ex) {
            Logger.getLogger(RoleDAO.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        } finally {
            con.CloseConnect(connect);
        }
    }

    public ArrayList<RoomDTO> readAll() {
        try {
            list = new ArrayList<RoomDTO>();
            connect = con.OpenConnect();
            PreparedStatement pst = connect.prepareStatement(SQL_READALL);
            ResultSet rs = pst.executeQuery();
            if (rs != null) {
                while (rs.next()) {
                    RoomDTO dto = new RoomDTO();
                    dto.setRoomid(rs.getInt(1));
                    dto.setRoomno(rs.getString(2));
                    dto.setRoomfloor(rs.getInt(3));
                    dto.setBeds(rs.getInt(4));
                    dto.setTypeid(rs.getInt(5));
                    list.add(dto);
                }
                return list;
            }
            return null;
        } catch (SQLException ex) {
            Logger.getLogger(RoleDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public RoomDTO readByID(int id) {
        try {
            connect = con.OpenConnect();
            PreparedStatement pst = connect.prepareStatement(SQL_READ_BYID);
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            if (rs == null) {
                return null;
            } else {
                if (rs.next()) {
                    RoomDTO dto = new RoomDTO();
                    dto.setRoomid(rs.getInt(1));
                    dto.setRoomno(rs.getString(2));
                    dto.setRoomfloor(rs.getInt(3));
                    dto.setBeds(rs.getInt(4));
                    dto.setTypeid(rs.getInt(5));
                    return dto;
                }
                return null;
            }
        } catch (SQLException ex) {
            Logger.getLogger(RoleDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public ArrayList<RoomDTO> readByTypeID(int id) {
        list = new ArrayList<RoomDTO>();
        try {
            connect = con.OpenConnect();
            PreparedStatement pst = connect.prepareStatement(SQL_READ_BY_TYPEID);
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            if (rs != null) {
                while (rs.next()) {
                    RoomDTO dto = new RoomDTO(rs.getInt(1), rs.getString(2),
                            rs.getInt(3), rs.getInt(4), rs.getInt(5));
                    list.add(dto);
                }
                return list;
            }

            return list;
        } catch (SQLException ex) {
            Logger.getLogger(LoginDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            con.CloseConnect(connect);
        }
    }

    public int update(RoomDTO dto) {
        try {
            connect = con.OpenConnect();
            PreparedStatement pst = connect.prepareStatement(SQL_UPDATE);
            pst.setString(1, dto.getRoomno());
            pst.setInt(2, dto.getRoomfloor());
            pst.setInt(3, dto.getBeds());
            pst.setInt(4, dto.getTypeid());
            return pst.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(RoleDAO.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        }
    }

    public int delete(int id) {
        try {
            connect = con.OpenConnect();
            PreparedStatement pst = connect.prepareStatement(SQL_DELETE);
            pst.setInt(1, id);
            return pst.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(RoleDAO.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        }
    }
}
